package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAsset;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.MProduct;
import org.compiere.model.MUOM;
import org.compiere.model.X_MM_Waybill;
import org.compiere.model.X_MM_WaybillLine;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

public class MM_Form_AT3 extends SvrProcess {
	//Form_AT3_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_MM_Waybill_ID;
	/** */
	String ResultPath;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("MM_Waybill_ID")) 
				p_MM_Waybill_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_AT3_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 10) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";		
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Style for cells
		CellStyle freeStyle = wb.createCellStyle();
		freeStyle.setBorderBottom((short)1);
		freeStyle.setBorderTop((short)1);
		freeStyle.setBorderRight((short)1);
		freeStyle.setBorderLeft((short)1);
		freeStyle.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		freeStyle.setWrapText(true);
		freeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		
		X_MM_Waybill waybill = new X_MM_Waybill(m_ctx, p_MM_Waybill_ID, get_TrxName());
		//Entering header
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(13).getCell(46).setCellValue(waybill.getDocumentNo());
		sheet.getRow(13).getCell(52).setCellValue(waybill.getMM_DateOf_Issue()); // == null ? " " : waybill.getMM_DateOf_Issue());
		sheet.getRow(13).getCell(58).setCellValue(waybill.getMM_OperationCode());
		sheet.getRow(20).getCell(0).setCellValue((new MAsset(m_ctx, waybill.getMM_Vehicle_ID(), get_TrxName()).getName()));
		sheet.getRow(20).getCell(7).setCellValue(waybill.getMM_Vehicle_RegNo());
		sheet.getRow(20).getCell(14).setCellValue(waybill.getMM_Vehicle_GarageNo());
		sheet.getRow(20).getCell(21).setCellValue(waybill.getMM_Vehicle_ColumnNo());
		sheet.getRow(20).getCell(28).setCellValue(waybill.getMM_Vehicle_BrigadeNo());
		sheet.getRow(20).getCell(34).setCellValue((new MProduct(m_ctx, waybill.getMM_ConsumpRate_FuelMark_ID(), get_TrxName()).getName()));
		sheet.getRow(20).getCell(40).setCellValue(waybill.getMM_ConsumpRate_Vehicle100km());
		sheet.getRow(20).getCell(52).setCellValue(waybill.getMM_ConsumpRate_Engine1h());
		sheet.getRow(20).getCell(58).setCellValue(waybill.getMM_ConsumpRate_COV());
		sheet.getRow(23).getCell(33).setCellValue((new MBPartner(m_ctx, waybill.getMM_Driver_ID(), get_TrxName()).getName()));
		sheet.getRow(23).getCell(58).setCellValue((new MBPartner(m_ctx, waybill.getMM_Driver_ID(), get_TrxName()).gethrm_tableNumber()));
		sheet.getRow(31).getCell(8).setCellValue(waybill.getMM_TimeOnSched_Checkout());
		sheet.getRow(32).getCell(8).setCellValue(waybill.getMM_TimeOnSched_Return());
		sheet.getRow(31).getCell(16).setCellValue(waybill.getMM_TimeOnFact_Checkout());
		sheet.getRow(32).getCell(16).setCellValue(waybill.getMM_TimeOnFact_Return());
		sheet.getRow(31).getCell(24).setCellValue(waybill.getMM_Speedometer_Checkout());
		sheet.getRow(32).getCell(24).setCellValue(waybill.getMM_Speedometer_Return());
		sheet.getRow(33).createCell(7).setCellValue(waybill.getMM_RepairOn_Way() == null ? " " : waybill.getMM_RepairOn_Way());
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT MM_WaybillLine_ID FROM MM_waybillLine"
			+ " WHERE MM_Waybill_ID = " + p_MM_Waybill_ID;
		
		try {
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			
			X_MM_WaybillLine i_waybillline = null;
			int row = 38;
			
			while (rs.next()) {
				row++;
				sheet.shiftRows(row, row + 41, 1);
				i_waybillline = new X_MM_WaybillLine(m_ctx, rs.getInt(1), get_TrxName());
				
				R = sheet.getRow(row);
				for(int i = 0; i< 64; i++) {
					C = R.createCell(i);
					C.setCellStyle(freeStyle);
				}
				
				//Merging
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 23));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 31));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 39));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 40, 47));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 48, 55));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 56, 63));
				
				//Entering First positions
				sheet.getRow(row).getCell(0).setCellValue((new MBPartner(m_ctx, i_waybillline.getMM_Customer_ID(), get_TrxName()).getName()));
				sheet.getRow(row).getCell(24).setCellValue((new SimpleDateFormat("hh:mm").format(i_waybillline.getMM_Task_TimeOf_Departure())));
				sheet.getRow(row).getCell(32).setCellValue((new SimpleDateFormat("hh:mm").format(i_waybillline.getMM_Task_TimeOf_Arrival())));
				sheet.getRow(row).getCell(40).setCellValue(i_waybillline.getMM_Task_Mileage());
				sheet.getRow(row+2).getCell(12).setCellValue((new MBPartner(m_ctx, waybill.getMM_Dispatcher_ID(),	get_TrxName()).getName()));
				//Entering Second positions
				sheet.getRow(row+11).getCell(0).setCellValue(i_waybillline.getMM_RouteMove_Departure());
				sheet.getRow(row+11).getCell(8).setCellValue(i_waybillline.getMM_RouteMove_Destination());
				sheet.getRow(row+11).getCell(16).setCellValue((new SimpleDateFormat("hh:mm").format(i_waybillline.getMM_Result_TimeOf_Departure())));
				sheet.getRow(row+12).getCell(16).setCellValue((new SimpleDateFormat("hh:mm").format(i_waybillline.getMM_Result_TimeOf_Arrival())));
				sheet.getRow(row+11).getCell(24).setCellValue(i_waybillline.getMM_Result_SpeedometerDeparture());
				sheet.getRow(row+12).getCell(24).setCellValue(i_waybillline.getMM_Result_SpeedometerArrival());
				sheet.getRow(row+11).getCell(32).setCellValue(i_waybillline.getMM_Result_Mileage_Zero());
				sheet.getRow(row+11).getCell(40).setCellValue(i_waybillline.getMM_Result_Mileage_Total());
				sheet.getRow(row+19).getCell(0).setCellValue(i_waybillline.getMM_OperationTime_Driver());
				sheet.getRow(row+19).getCell(8).setCellValue(i_waybillline.getMM_OperationTime_Engine());
				sheet.getRow(row+19).getCell(16).setCellValue(i_waybillline.getMM_Downtime_End().getTime() - i_waybillline.getMM_Downtime_Begin().getTime()); 
				sheet.getRow(row+19).getCell(24).setCellValue(i_waybillline.getMM_Downtime_Reason());
				
				//Entering consumption
				sheet.getRow(65).getCell(0).setCellValue((new MProduct(m_ctx, waybill.getMM_FuelMark_ID(), get_TrxName()).getName()));
				sheet.getRow(65).getCell(7).setCellValue((new MProduct(m_ctx, waybill.getMM_FuelMark_ID(), get_TrxName()).getValue()));
				
				sheet.getRow(65).getCell(14).setCellValue((new MUOM(m_ctx, (new MProduct(m_ctx, waybill.getMM_FuelMark_ID(), get_TrxName()).getC_UOM_ID()), get_TrxName()).getName()));
				sheet.getRow(65).getCell(21).setCellValue((new MUOM(m_ctx, (new MProduct(m_ctx, waybill.getMM_FuelMark_ID(), get_TrxName()).getC_UOM_ID()), get_TrxName()).getX12DE355()));
				
				sheet.getRow(row+26).getCell(28).setCellValue(waybill.getMM_FuelRemainder_Checkout());
				sheet.getRow(row+26).getCell(35).setCellValue(waybill.getMM_FuelRemainder_Return());
				sheet.getRow(row+26).getCell(42).setCellValue(waybill.getMM_Fuel_Issue());
				sheet.getRow(row+26).getCell(56).setCellValue(waybill.getMM_Fuel_Issued());
				sheet.getRow(row+28).getCell(9).setCellValue((String)i_waybillline.getMM_Coupons_Issued());
				sheet.getRow(row+31).getCell(23).setCellValue((new MBPartner(m_ctx, waybill.getMM_Dispatcher_ID(), get_TrxName()).getName()));
			}
		}
		catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);			
		}
		finally {
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
	
		//---------------------------------------------------------------
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}
}
